const QUERY_SQL = `select * from user`;
const INSERT_SQL = `INSERT INTO customers SET ?`;
const UPDATE_SQL = `UPDATE t_user SET username=? WHERE id=?`;
const DELETE_SQL = `DELETE FROM t_user WHERE id=?`;

/**
 * 用户管理模块SQL
 */
// 查询用户列表
const USERLIST_SQL = `select user_id, user_name, user_sex, user_phone, user_age, user.dept_id, dept_name, role from user join dept on user.dept_id=dept.dept_id`;
// 用户角色查询列表
const USERROLELIST_SQL = `select user_id, user_name, user_sex, user_phone, user_age, user.dept_id, dept_name, role from user join dept on user.dept_id=dept.dept_id where role=?`;
// 使用用户信息
const USERINFO_SQL = `select user_id, user_name, user_sex, user_phone, user_age, user.dept_id, role from user cross join dept on user.dept_id=dept.dept_id where user_id=?`;
// 添加用户
const USERADD_SQL = `insert into user set ?`;
// 修改用户
const USERUPDATE_SQL = `update user set user_name=?, user_sex=?, user_phone=?, user_age=?, dept_id=? where user_id=?`;
// 修改密码
const PASSWORDUPDATE_SQL = `update user set password=? where user_id=?`;
// 删除用户
const USERDELETE_SQL = `delete from user where user_id=?`;
// 服务端使用
const USERINFO_SERVE = `select * from user cross join dept on user.dept_id=dept.dept_id where user_id=?`;

/**
 * 系别模块SQL
 */
// 系别列表
const DEPTLIST_SQL = `select * from dept`;
// 查询系别信息
const DEPTINFO_SQL = `select * from dept where dept_id=?`;
// 添加系别
const DEPTADD_SQL = `insert into dept set ?`;
// 修改系别
const DEPTUPDATE_SQL = `update dept set dept_name=? where dept_id=?`;
// 删除系别
const DEPTDELETE_SQL = `delete from dept where dept_id=?`;

/**
 * 课程模块
 */
// 查询课程表
const COURSELIST_SQL = `select * from course`;
// 根据token查选课情况映射到课程表中
const ELECTIVEUSERID_SQL = `select course.course_id,course.course_name,course.course_credit,course.spare_time,case when course.course_id=elective.course_id and elective.user_id=? then 1 else 0 end as isElective from mysql_test.course left join mysql_test.elective on course.course_id=elective.course_id and elective.user_id=?;`;
// 课程详情
const COURSEINFO_SQL = `select * from course where course_id=?`;
// 根据token和课程号查选课情况映射到课程表中
const ELECTIVEUSERIDINFO_SQL = `select course.course_id,course.course_name,course.course_credit,course.spare_time,case when course.course_id=elective.course_id and elective.user_id=? then 1 else 0 end as isElective from course,elective where course.course_id=? and course.course_id=elective.course_id and elective.user_id=?;`;
// 添加课程
const COURSEADD_SQL = `insert into course set ?`;
// 修改课程
const COURSEUPDATE_SQL = `update course set course_name=?, course_credit=? where course_id=?`;
// 修改课余量 
const COURSESPARETIME_SQL = `update course set spare_time=? where course_id=?`;
// 删除课程
const COURSEDELETE_SQL = `delete from course where course_id=?`;
/**
 * 选课模块
 */
// 选课列表
const ELECTIVELIST_SQL = `select elective.user_id, elective.course_id, elective.grade, user.user_name, course.course_name from user,course,elective where elective.user_id=user.user_id and elective.course_id=course.course_id`;
// 根据用户查成绩
const ELECTIVEUSERLIST_SQL = `select elective.user_id, elective.course_id, elective.grade, user.user_name, course.course_name from user,course,elective where elective.user_id=user.user_id and elective.course_id=course.course_id and elective.user_id=?`;
// 根据成绩查选课
const ELECTIVEGRADE_SQL = `select elective.user_id, elective.course_id, elective.grade, user.user_name, course.course_name from user,course,elective where elective.user_id=user.user_id and elective.course_id=course.course_id and elective.grade=?`;

// 选课详情
const ELECTIVEINFO_SQL = `select elective.user_id, elective.course_id, elective.grade, user.user_name, course.course_name from user,course,elective where elective.user_id=user.user_id and elective.course_id=course.course_id and elective.user_id=? and elective.course_id=?`;
// 添加选课
const ELECTIVEADD_SQL = `insert into elective set ?`;
// 修改成绩
const ELECTIVEUPDATE_SQL = `update elective set grade=? where user_id=? and course_id=?`;
// 删除选课
const ELECTIVEDELETE_SQL = `delete from elective where user_id=? and course_id=?`;

module.exports = {
  QUERY_SQL,
  INSERT_SQL,
  UPDATE_SQL,
  DELETE_SQL,

  // 用户模块
  USERLIST_SQL,
  USERROLELIST_SQL,
  USERINFO_SQL,
  USERADD_SQL,
  USERUPDATE_SQL,
  PASSWORDUPDATE_SQL,
  USERDELETE_SQL,
  USERINFO_SERVE,
  // 系别模块
  DEPTLIST_SQL,
  DEPTINFO_SQL,
  DEPTADD_SQL,
  DEPTUPDATE_SQL,
  DEPTDELETE_SQL,
  // 课程模块
  COURSELIST_SQL,
  ELECTIVEUSERID_SQL,
  ELECTIVEUSERIDINFO_SQL,
  COURSEINFO_SQL,
  COURSEADD_SQL,
  COURSEUPDATE_SQL,
  COURSESPARETIME_SQL,
  COURSEDELETE_SQL,
  // 选课模块
  ELECTIVELIST_SQL,
  ELECTIVEGRADE_SQL,
  ELECTIVEINFO_SQL,
  ELECTIVEADD_SQL,
  ELECTIVEUPDATE_SQL,
  ELECTIVEDELETE_SQL,
  ELECTIVEUSERLIST_SQL,
};
